/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.deployment.daoImpl;

import br.com.deployment.dao.QuestionnaireResponseDAO;
import br.com.deployment.entities.Executor;
import br.com.deployment.entities.NotaryOffice;
import br.com.deployment.entities.QuestionnaireResponse;
import br.com.deployment.genericdao.GenericDAO;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author ely
 */
@Stateless
@LocalBean
public class QuestionnaireResponseDAOImpl extends GenericDAO<QuestionnaireResponse> implements QuestionnaireResponseDAO {
    
    public QuestionnaireResponseDAOImpl() {
        super(QuestionnaireResponse.class);
    }
    
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponse(QuestionnaireResponse questionnaireResponse) {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.id = :id   ";
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("id",questionnaireResponse.getId());
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponseNotFinnaly() {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false   ";
        Query query = getEntityManager().createQuery(sql);
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponseDate(Date date) {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false "
                + "and q.dateQ = :data";
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("data", date);
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponseNext(Date date) {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false and q.dateQ > :data ";
        
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("data", date);
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponsePending(Date date) {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false and q.dateQ < :data";
        
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("data", date);
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    //TODO Apos teste descomentar executor
    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponseExecutor(Executor executor) {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false";// and "
//                + " q.executor.id = :executor ";
        
        Query query = getEntityManager().createQuery(sql);
//        query.setParameter("executor", executor.getId());
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    public List<QuestionnaireResponse> findQuestionnaireResponseExecutorPendent(Executor executor){
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = false and "
                + " q.executor.id = :executor";
        
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("executor", executor.getId());
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }
    
    @Override
    public List<QuestionnaireResponse> searchQuestionnaireResponse(QuestionnaireResponse questionnaireResponse) {
        List<QuestionnaireResponse> lista = new ArrayList<QuestionnaireResponse>();
        
        String sql = "SELECT r FROM QuestionnaireResponse r WHERE ";
        
        if ((!questionnaireResponse.getExecutor().getName().equals("")) && (questionnaireResponse.getExecutor().getName() != null)) {
            sql += " lower(r.executor.name) like lower(:name) and  ";
        }
        
        if ((!questionnaireResponse.getNotaryOffice().getNome().equals("")) && (questionnaireResponse.getNotaryOffice().getNome() != null)) {
            sql += " lower(r.notaryOffice.nome) like lower(:nome) and  ";
        }
        
        if ((!questionnaireResponse.getNotaryOffice().getTitular().equals("")) && (questionnaireResponse.getNotaryOffice().getTitular() != null)) {
            sql += " lower(r.notaryOffice.titular) like lower(:titular) and  ";
        }
        
        
        sql += " 1=1 ";
        System.err.println(sql);
        Query query = getEntityManager().createQuery(sql);
        
        if ((!questionnaireResponse.getExecutor().getName().equals("")) && (questionnaireResponse.getExecutor().getName() != null)) {
            query.setParameter("name", "%" + questionnaireResponse.getExecutor().getName() + "%");
        }
        
        if ((!questionnaireResponse.getNotaryOffice().getNome().equals("")) && (questionnaireResponse.getNotaryOffice().getNome() != null)) {
            query.setParameter("nome", "%" + questionnaireResponse.getNotaryOffice().getNome() + "%");
        }
        
        if ((!questionnaireResponse.getNotaryOffice().getTitular().equals("")) && (questionnaireResponse.getNotaryOffice().getTitular() != null)) {
            query.setParameter("titular", "%" + questionnaireResponse.getNotaryOffice().getTitular() + "%");
        }
        
        
        lista = query.getResultList();
        System.err.println(sql);
        return lista;
    }

    @Override
    public List<QuestionnaireResponse> findQuestionnaireResponseFinnaly() {
        String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.states = true  ";
        Query query = getEntityManager().createQuery(sql);
        return (List<QuestionnaireResponse>) query.getResultList();
    }

    @Override
    public List<QuestionnaireResponse> findQNotaryOffice(NotaryOffice notaryOffice) {
         String sql = "SELECT q FROM QuestionnaireResponse q JOIN q.notaryOffice n WHERE "
                + " n.id = :id ";
        
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("id", notaryOffice.getId());
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }

    @Override
    public List<QuestionnaireResponse> findDate(Date dataIni, Date dataFim) {
         String sql = "SELECT q FROM QuestionnaireResponse q WHERE q.dateQ between :dataIni "
                      + "AND :dataFim ";
               
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("dataIni", dataIni);
        query.setParameter("dataFim", dataFim);
        
        return (List<QuestionnaireResponse>) query.getResultList();
    }
}
